const express = require('express')
const { getAll, getOne, exec } = require('../db')

var router = express.Router()

// 获取文章
router.get('/', async (req, res) => {
  // 解析请求参数
  const { page = 1, size = 5 } = req.query

  let sql = `select count (*) as total from blog_articles`
  let { total } = await getOne(sql)
  // 偏移量
  let offset = (page - 1) * size
  sql = `select  a.*,b.type from blog_articles as a,blog_category as b where a.category_id = b.id limit ${offset}, ${size}`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取文章成功',
    result: { total, data },
  })
})

// 搜索
router.get('/search', async (req, res) => {
  const { page = 1, size = 5, keywords } = req.query
  let sql = `SELECT count(*) as total FROM blog_articles WHERE CONCAT(author,title,category_id) LIKE '%${keywords}%'`
  let { total } = await getOne(sql)
  // 偏移量
  let offset = (page - 1) * size
  sql = `select a.*,b.type from blog_articles as a,blog_category as b  where a.category_id = b.id and  concat(author,title,category_id) like '%${keywords}%' limit ${offset}, ${size}`
  console.log(sql)
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '搜索成功',
    result: {
      total,
      data,
    },
  })
})

// 根据id获取单个用户
router.get('/:id', async (req, res) => {
  console.log('abc')
  // 一. 解析参数
  let { id } = req.params

  // 二. 操作数据库
  let sql = `select * from blog_articles where id=${id}`
  const data = await getOne(sql)

  // 三. 返回结果
  res.send({ result: data })
})

// 新增文章
router.post('/', async (req, res) => {
  // 一. 解析参数
  const { author, title, content, category_id, imgPath } = req.body
  // 二. 操作数据库
  let sql = `insert into blog_articles (author, title, content, category_id,imgPath) 
  values ('${author}','${title}','${content}','${category_id}','${imgPath}')`
  const data = await exec(sql)
  // 三. 返回结果
  res.send({
    code: 0,
    message: '添加文章成功',
    result: {
      id: data.insertId,
      author,
      title,
      content,
      category_id,
      imgPath,
    },
  })
})

router.put('/select', async (req, res) => {
  const { id, selected } = req.body
  let sql = `update blog_articles set selected = '${selected}' where id = ${id}`
  await exec(sql)
  res.send({
    code: 0,
    message: '修改状态成功',
    result: {
      id,
      selected,
    },
  })
})

// 修改文章
router.put('/:id', async (req, res) => {
  // 一. 解析参数
  let { id } = req.params
  let { author, title, content, category_id, imgPath } = req.body
  // 二. 操作数据库
  let sql = `update blog_articles set author='${author}', content='${content}', title='${title}', category_id='${category_id}', imgPath='${imgPath}' where id=${id}`
  const resData = await exec(sql)
  // 三. 返回结果
  console.log(resData)
  res.send({
    code: 0,
    message: '修改文章成功',
    result: {
      id,
      author,
      content,
      title,
      category_id,
      imgPath,
    },
  })
})

router.delete('/delselected', async (req, res) => {
  let sql = `delete from blog_articles where selected = 1`
  await exec(sql)
  res.send({
    code: 0,
    message: '删除选中成功',
    result: '',
  })
})

// 删除单个文章
router.delete('/:id', async (req, res) => {
  // 一. 解析参数
  let { id } = req.params
  // 二. 操作数据库
  let sql = `delete from blog_articles where id = '${id}'`
  await exec(sql)
  // 三. 返回结果
  res.send({
    code: 0,
    message: '删除成功',
    result: '',
  })
})

module.exports = router
